<?php
/**
 * database (mysql) function class
 *
 * PHP version 5
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @version   SVN: $Id: mysql.function.class.php 110 2010-07-23 14:57:13Z sruttloff@googlemail.com $
 * @link      phpmy
 */
/**
 * mysql known function and function check
 *
 * @category  PHP
 * @package   PHPMY
 * @author    Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright 2010 Sven Ruttloff, www.sruttloff.de
 * @license   http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 * @link      phpmy
 */
class mysqlFunction
{
    /**
     * @var known functions for mysql
     */
    public $MYSQLFUNCTIONS = array (
        'ABS',
        'ACOS',
        'ADDDATE',
        'ADDTIME',
        'AES_DECRYPT',
        'AES_ENCRYPT',
        'AREA',
        'ASCII',
        'ASIN',
        'ATAN',
        'ATAN2',
        'AVG',
        'BENCHMARK',
        'BIN',
        'BIT_AND',
        'BIT_COUNT',
        'BIT_LENGTH',
        'BIT_OR',
        'BIT_XOR',
        'Boundary',
        'CAST',
        'CEIL',
        'CEILING',
        'CENTROID', // multipolygon-property-functions.html
        'CHAR', // string-functions.html
        'CHARACTER_LENGTH',
        'CHARSET', // information-functions.html
        'CHAR_LENGTH',
        'COALESCE',
        'COERCIBILITY', // information-functions.html
        'COLLATION', // information-functions.html
        'COMPRESS', // string-functions.html
        'CONCAT',
        'CONCAT_WS',
        'CONNECTION_ID',
        'CONV',
        'CONVERT',
        'CONVERT_TZ',
        'COS',
        'COT',
        'COUNT',
        'CRC32', // mathematical-functions.html
        'CURDATE',
        'CURRENT_DATE',
        'CURRENT_TIME',
        'CURRENT_TIMESTAMP',
        'CURRENT_USER',
        'CURTIME',
        'DATABASE',
        'DATE', // date-and-time-functions.html
        'DATEDIFF', // date-and-time-functions.html
        'DATE_ADD',
        'DATE_DIFF',
        'DATE_FORMAT',
        'DATE_SUB',
        'DAY',
        'DAYNAME',
        'DAYOFMONTH',
        'DAYOFWEEK',
        'DAYOFYEAR',
        'DECODE',
        'DEFAULT', // miscellaneous-functions.html
        'DEGREES',
        'DES_DECRYPT',
        'DES_ENCRYPT',
        'DIMENSION', // general-geometry-property-functions.html
        'ELT',
        'ENCODE',
        'ENCRYPT',
        'ENDPOINT', // linestring-property-functions.html
        'ENVELOPE', // general-geometry-property-functions.html
        'EXP',
        'EXPORT_SET',
        'EXTERIORRING', // polygon-property-functions.html
        'EXTRACT',
        'FIELD',
        'FIND_IN_SET',
        'FLOOR',
        'FORMAT',
        'FOUND_ROWS',
        'FROM_DAYS',
        'FROM_UNIXTIME',
        'GEOMETRYN', // geometrycollection-property-functions.html
        'GEOMETRYTYPE', // general-geometry-property-functions.html
        'GET_FORMAT',
        'GET_LOCK',
        'GLENGTH', // linestring-property-functions.html
        'GREATEST',
        'GROUP_CONCAT',
        'GROUP_UNIQUE_USERS',
        'HEX',
        'HOUR',
        'IF', //control-flow-functions.html
        'IFNULL',
        'INET_ATON',
        'INET_NTOA',
        'INSERT', // string-functions.html
        'INSTR',
        'INTERIORRINGN', // polygon-property-functions.html
        'INTERVAL',
        'ISCLOSED', // multilinestring-property-functions.html
        'ISEMPTY', // general-geometry-property-functions.html
        'ISNULL',
        'ISRING', // linestring-property-functions.html
        'ISSIMPLE', // general-geometry-property-functions.html
        'IS_FREE_LOCK',
        'IS_USED_LOCK', // miscellaneous-functions.html
        'LAST_DAY',
        'LAST_INSERT_ID',
        'LCASE',
        'LEAST',
        'LEFT',
        'LENGTH',
        'LN',
        'LOAD_FILE',
        'LOCALTIME',
        'LOCALTIMESTAMP',
        'LOCATE',
        'LOG',
        'LOG10',
        'LOG2',
        'LOWER',
        'LPAD',
        'LTRIM',
        'MAKEDATE',
        'MAKETIME',
        'MAKE_SET',
        'MASTER_POS_WAIT',
        'MAX',
        'MD5',
        'MICROSECOND',
        'MID',
        'MIN',
        'MINUTE',
        'MOD',
        'MONTH',
        'MONTHNAME',
        'NOW',
        'NULLIF',
        'NUMGEOMETRIES', // geometrycollection-property-functions.html
        'NUMINTERIORRINGS', // polygon-property-functions.html
        'NUMPOINTS', // linestring-property-functions.html
        'OCT',
        'OCTET_LENGTH',
        'OLD_PASSWORD',
        'ORD',
        'PASSWORD',
        'PERIOD_ADD',
        'PERIOD_DIFF',
        'PI',
        'POINTN', // linestring-property-functions.html
        'POINTONSURFACE', // multipolygon-property-functions.html
        'POSITION',
        'POW',
        'POWER',
        'QUARTER',
        'QUOTE',
        'RADIANS',
        'RAND',
        'RELEASE_LOCK',
        'REPEAT',
        'REPLACE', // string-functions.html
        'REVERSE',
        'RIGHT',
        'ROUND',
        'ROW_COUNT', // information-functions.html
        'RPAD',
        'RTRIM',
        'SCHEMA', // information-functions.html
        'SECOND',
        'SEC_TO_TIME',
        'SESSION_USER',
        'SHA',
        'SHA1',
        'SIGN',
        'SIN',
        'SLEEP', // miscellaneous-functions.html
        'SOUNDEX',
        'SPACE',
        'SQRT',
        'SRID', // general-geometry-property-functions.html
        'STARTPOINT', // linestring-property-functions.html
        'STD',
        'STDDEV',
        'STDDEV_POP', // group-by-functions.html
        'STDDEV_SAMP', // group-by-functions.html
        'STRCMP',
        'STR_TO_DATE',
        'SUBDATE',
        'SUBSTRING',
        'SUBSTRING_INDEX',
        'SUBTIME',
        'SUM',
        'SYSDATE',
        'SYSTEM_USER',
        'TAN',
        'TIME',
        'TIMEDIFF',
        'TIMESTAMP',
        'TIMESTAMPADD',
        'TIMESTAMPDIFF',
        'TIME_FORMAT',
        'TIME_TO_SEC',
        'TO_DAYS',
        'TRIM',
        'TRUNCATE', // mathematical-functions.html
        'UCASE',
        'UNCOMPRESS', // string-functions.html
        'UNCOMPRESSED_LENGTH', // string-functions.html
        'UNHEX', // string-functions.html
        'UNIQUE_USERS',
        'UNIX_TIMESTAMP',
        'UPPER',
        'USER',
        'UTC_DATE',
        'UTC_TIME',
        'UTC_TIMESTAMP',
        'UUID', // miscellaneous-functions.html
        'VARIANCE', // group-by-functions.html
        'VAR_POP', // group-by-functions.html
        'VAR_SAMP', // group-by-functions.html
        'VERSION',
        'WEEK',
        'WEEKDAY',
        'WEEKOFYEAR',
        'X', // point-property-functions.html
        'Y', // point-property-functions.html
        'YEAR',
        'YEARWEEK'
    );
    /**
     * check if cell value is a function
     *
     * @param string $v cell value
     *
     * @return string quotet field value or unqu. if a function
     */
    public function isMYSQLFunction($v)
    {
        // already quoted
        if (preg_match("=^('|\").*('|\")$=", $v))
            return substr($v, 1, (strlen($v)-2));
        if (is_null($v))
            return 'NULL';
        if ($v == 'NULL')
            return $v;
        // is value with brackets
        if (preg_match('=^.+\(.*\).*$=', $v))
        {
            // is the value a mysql function
            if (!in_array(preg_replace('=\(.*\)=si', '', $v), $this->MYSQLFUNCTIONS))
                return "'" . $v . "'";
            else
                return $v;
        }
        return "'" . $v . "'";
    }
}